/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package idea.model.dbmanager;


import idea.model.qnrlibrary.CustomContentItemSearchView;
import idea.model.qnrlibrary.CustomContentItems;
import idea.model.qnrlibrary.CustomContentLayouts;
import idea.model.qnrlibrary.CustomResponseDeView;
import idea.model.qnrlibrary.QuestionTitle;
import idea.model.qnrlibrary.QuestionTitleLinkView;
import idea.model.qnrlibrary.QuestionTitleSearchView;
import idea.model.qnrlibrary.Questionnaires;
import idea.model.qnrlibrary.ReportingPeriods;
import java.io.IOException;
import java.io.Reader;
import java.math.BigInteger;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.swing.ImageIcon;
import org.openide.util.Exceptions;


/**
 * A Manager class which builds and runs all CRUD Java Persistence queries
 *
 * @author rejun001
 * @Note: most methods are static
 */
public class QueryManager
{

    /**
     * object which builds a singleton EntityManager based on login info.
     */
    private static EntityManBuilder emb = null;
    /**
     * Entity Manager to operate Qnr Library persistence unit
     */
    private static EntityManager entityManager = null; 
    /**
     * The clob (containing the SFO representing the custom content layout)
     */
    private Clob clob = null;
    /**
     * The blob (containing the Jpeg image of the custom content layout)
     */
    private Blob jpegBlob = null;
    /**
     * The SFO clob in String form
     */
    private String clobStr = null;
    /**
     * The transaction for these DB actions
     */
    private static EntityTransaction transaction = null;


    /**
     * simple Class Constructor.
     */
    public QueryManager()
    {
    }


    /**
     * if necessary, builds the JPA EntityManager using login info 
     * (otherwise default).  Prints the login info to output for now
     * 
     * @return EntityManager
     */
    public static EntityManager getEntityManager()
    {
        emb = new EntityManBuilder();
        entityManager = emb.getEntityManager();

        Map<String , Object> myMap = entityManager.getProperties();
        System.out.println( "Entity properties: " );
        for ( String key : myMap.keySet() )
        {
            System.out.println( "Key " + key + "=> " + myMap.get( key ).toString() );
        }

        return entityManager;
    }


    /**
     * gets all surveys. Used to populate the survey ComboBox in OpeningEditor.
     * 
     *
     * @return List of Strings (four letter abbreviations, for example "COSA")
     */
    public static List<String> getAllSurveys()
    {
        //Query query = entityManager.createQuery( "SELECT DISTINCT q.survey FROM Questionnaires q ORDER BY q.survey ASC" );
        Query query = getEntityManager().createQuery( "SELECT DISTINCT q.survey FROM Questionnaires q ORDER BY q.survey ASC" );
        return query.getResultList();
    }


    /**
     * gets the appropriate RefPeriods for this survey. Populates the RefPeriod
     * ComboBox in OpeningEditor.
     *
     * @param  survey
     * @return List of Strings (for example "2012 Survey")
     */
    public static List<String> getRefPeriods( String survey )
    {
        //return entityManager.createQuery(
        return getEntityManager().createQuery(
                "SELECT DISTINCT q.refPeriod "
                + "FROM Questionnaires q "
                + "WHERE q.survey = :survey " )
                .setParameter( "survey" , survey )
                .getResultList();
    }


    /**
     * Gets the Form Numbers for this Survey/RefPeriod NOT USED -- But keeping
     * this because of JPA weirdness about refPeriod
     *
     * @param survey
     * @param refPeriod -- a ReportingPeriods (this is a join under the covers)
     * @return a list of all Form Numbers for a given survey and
     * ReportingPeriods' ref period, to populate ComboBox. Strings.
     */
    public static List<String> getFormNumbers( String survey , 
            ReportingPeriods refPeriod )
    {
        //return entityManager.createQuery(
        return getEntityManager().createQuery(
                "SELECT DISTINCT q.formNumber "
                + "FROM Questionnaires q "
                + "WHERE q.survey = :survey AND q.refPeriod = :refPeriod ORDER BY q.formNumber" )
                .setParameter( "survey" , survey )
                .setParameter( "refPeriod" , refPeriod )
                .getResultList();
    }


    /**
     * gets the appropriate Form Numbers for this Survey/RefPeriod.  Populates
     * the Form Number ComboBox in OpeningEditor
     *
     * @param survey
     * @param refPeriod
     * @return List of Strings (for example, "MA-10000")
     */
    public static List<String> getFormNumbers( String survey , String refPeriod )
    {
        //return entityManager.createQuery(
        return getEntityManager().createQuery(
                "SELECT DISTINCT q.formNumber "
                + "FROM Questionnaires q "
                + "WHERE q.survey = :survey AND q.refPeriod = :refPeriod ORDER BY q.formNumber" )
                .setParameter( "survey" , survey )
                .setParameter( "refPeriod" , refPeriod )
                .getResultList();
    }


    /**
     * gets the Questions for this Questionnaire.  Displayed in a table for
     * the given Questionnaire.
     *
     * @param qnrId - Id for the Questionnaires object
     * @return List of QuestionTitleLinkView objects (a view containing
     *         basically QuestionTitles plus some other displayed info.)
     */
    public static List<QuestionTitleLinkView> getQuestionTitles( String qnrId )
    {
        return entityManager.createQuery(
                "SELECT q FROM QuestionTitleLinkView q "
                + "WHERE q.qnrId = :qnrId ORDER BY q.questSequence" )
                .setParameter( "qnrId" , qnrId )
                .getResultList();
    }


    /**
     * gets the QuestionTitle object associated with this questId.
     * Should be only one.
     *
     * @param questId
     * @return List of QuestionTitle objects. 
     */
    public static List<QuestionTitle> getQT( String questId )
    {
        //return entityManager.createQuery(
        return getEntityManager().createQuery(
                "SELECT qt FROM QuestionTitle qt "
                + "WHERE qt.questId = :questId" )
                .setParameter( "questId" , questId )
                .getResultList();
    }

    /**
     * gets all the CustomContentItems whose questId matches the one
     * passed in as parameter.
     * <p/>
     * @param questId Question Identifier
     * @return List of CustomContentItem objects 
     */
    public static List<CustomContentItems> getCustomContentItemsList(
            String questId )
    {
        List<CustomContentItems> customContentItems = new ArrayList<>();

        /*
         * The CustomContentItems table has an entry for the Questionnaire
         * Identifier, howerver the persistance layer requires a QuestionTitle
         * object, not the string we have provided as parameters.
         * "SingularAttribute<CustomContentItems , QuestionTitle> questId;"
         *
         * So we need to get a list of QuestionTitles first, and then use this
         * to get the list of matching CustomContentItems.
         */
        //List<QuestionTitle> questionTitles = QueryManager.getQT( questId );
        List<QuestionTitle> questionTitles = getEntityManager().createNamedQuery(
                "QuestionTitle.findByQuestId" , QuestionTitle.class ).
                setParameter( "questId" , questId ).
                getResultList();

        for ( QuestionTitle qTitle : questionTitles )
        {
            // CustomContentItems.findByQuestId
            customContentItems.addAll(
                    getEntityManager().createNamedQuery(
                    "CustomContentItems.findByQuestId" ,
                    CustomContentItems.class ).
                    setParameter( "questId" , qTitle ).
                    getResultList() );
        }

//        return entityManager.createQuery(
//                "SELECT c FROM CustomContentItems c "
//                + "WHERE c.questId = :questId ORDER BY c.itemSequence" )
//                .setParameter( "questId" , questId )
//                .getResultList();
        return customContentItems;
    }

    /**
     * gets the CCIs for this question
     *
     * @param questId -- the QuestionTitle the Id points to
     * @return a list of all CustomContentItems objects to display in a table
     * for a given Question.
     */
    public static List<CustomContentItems> getCustomContentItems( QuestionTitle questId )
    {
        //return entityManager.createQuery(
        return getEntityManager().createQuery(
                "SELECT c FROM CustomContentItems c "
                + "WHERE c.questId = :questId ORDER BY c.itemSequence" )
                .setParameter( "questId" , questId )
                .getResultList();
    }


    /**
     * gets the data elements to display in a
     * table for a given Custom Content Item.
     *
     * @param itemId - Id of a CustomContentItem
     * @return List of CustomResponseDeView objects (a view of combined
     *         data from CustomResponse and DataElement and other tables)
     */
    public static List<CustomResponseDeView> getDataElements( String itemId )
    {
        //return entityManager.createQuery(
        return getEntityManager().createQuery(
                "SELECT d FROM CustomResponseDeView d "
                + "WHERE d.itemId = :itemId ORDER BY d.occurrenceSequence" )
                .setParameter( "itemId" , itemId )
                .getResultList();
    }


    /**
     * gets the CustomContentLayouts (containing SFO, JPEG) for this question
     *
     * @param questId - a QuestionTitle object
     * @return List of CustomContentLayout objects 
     */
    public static List<CustomContentLayouts> getCustomContentLayouts( QuestionTitle questId )
    {
        //return entityManager.createQuery(
        return getEntityManager().createQuery(
                "SELECT c FROM CustomContentLayouts c "
                + "WHERE c.questId = :questId AND "
                + " c.formMedia = 'PAPER'" )
                .setParameter( "questId" , questId )
                .getResultList();
    }


    /**
     * builds the questionnaire query for the criteria chosen in the
     * OpeningEditorTopComponent based on the parms (from TextFields and
     * ComboBoxes).  If the TextBox is empty or the ComboBox entry is
     * blank, all objects are queried for.
     *
     * The following are criteria fields:
     * @param survey
     * @param title
     * @param subTitle
     * @param formNumber
     * @param refPeriod
     * @param createdBy
     * @return List of Questionnaires object results
     */
    public static List<Questionnaires> buildQnrQuery( String survey , String title , String subTitle , String formNumber , String refPeriod , String createdBy )
    {
        Query myQuery;
        final String OPEN_BRACKET = "[";
        boolean useRPs = false;   // unlikely: using a ReportingPeriods reference in the Questionnaires table instead of a regular String

        // if no selected fields, return everything
        if ( ( survey == null || survey.isEmpty() || survey.startsWith( OPEN_BRACKET ) )
                && ( subTitle == null || subTitle.isEmpty() || subTitle.startsWith( OPEN_BRACKET ) )
                && ( title == null || title.isEmpty() || title.startsWith( OPEN_BRACKET ) )
                && ( refPeriod == null || refPeriod.isEmpty() || refPeriod.startsWith( OPEN_BRACKET ) )
                && ( createdBy == null || createdBy.isEmpty() || createdBy.startsWith( OPEN_BRACKET ) )
                && ( formNumber == null || formNumber.isEmpty() || formNumber.startsWith( OPEN_BRACKET ) ) )
        {
            //myQuery = entityManager.createNamedQuery( "Questionnaires.findAll" );
            myQuery = getEntityManager().createNamedQuery( "Questionnaires.findAll" );
        }
        else
        {
            // build the query
            StringBuilder sb = new StringBuilder( "SELECT q FROM Questionnaires q" );

            String where = " WHERE";
            String and = " AND";
            String conj = where;

            boolean hasSurvey = false;
            boolean hasTitle = false;
            boolean hasSubTitle = false;
            boolean hasCreatedBy = false;
            boolean hasFormNumber = false;
            boolean hasRefPeriod = false;

            String addSurvey = "";
            String addTitle = "";
            String addSubtitle = "";
            String addCreatedBy = "";
            String addFormNum = "";
            String addRefPeriod = "";

            if ( !survey.isEmpty() )
            {
                survey = addWildcards( survey );
                addSurvey = conj + " q.survey LIKE :survey";
                conj = and;
                hasSurvey = true;
            }
            if ( !subTitle.isEmpty() )
            {
                subTitle = addWildcards( subTitle.toUpperCase() );
                addSubtitle = conj + " UPPER(q.subTitle) LIKE :subTitle";
                conj = and;
                hasSubTitle = true;
            }
            if ( !title.isEmpty() )
            {
                title = addWildcards( title.toUpperCase() );
                addTitle = conj + " UPPER(q.title) LIKE :title";
                conj = and;
                hasTitle = true;
            }
            if ( !createdBy.isEmpty() )
            {
                createdBy = addWildcards( createdBy.toUpperCase() );
                addCreatedBy = conj + " UPPER(q.createdBy) LIKE :createdBy";
                conj = and;
                hasCreatedBy = true;
            }
            if ( !refPeriod.isEmpty() )
            {

                refPeriod = addWildcards( refPeriod.toUpperCase() );
                if ( useRPs )
                {
                    // Note that this one is getting refPeriod from a ReportingPeriods object called refPeriod,
                    //  not a direct String.
                    addRefPeriod = conj + " UPPER(q.refPeriod.refPeriod) LIKE :refPeriod";
                }
                else
                {
                    addRefPeriod = conj + " UPPER(q.refPeriod) LIKE :refPeriod";
                }
                conj = and;
                hasRefPeriod = true;
            }
            if ( !formNumber.isEmpty() )
            {
                formNumber = addWildcards( formNumber.toUpperCase() );
                addFormNum = conj + " UPPER(q.formNumber) LIKE :formNumber";
                //conj = and;
                hasFormNumber = true;
            }
            sb.append( addSurvey );
            sb.append( addSubtitle );
            sb.append( addTitle );
            sb.append( addFormNum );
            sb.append( addRefPeriod );
            sb.append( addCreatedBy );
            String query = sb.toString();

            // create the Entity Manager Query
            myQuery = entityManager.createQuery( query );
            if ( hasSurvey )
            {
                myQuery = myQuery.setParameter( "survey" , survey );
            }
            if ( hasTitle )
            {
                myQuery = myQuery.setParameter( "title" , title );
            }
            if ( hasSubTitle )
            {
                myQuery = myQuery.setParameter( "subTitle" , subTitle );
            }
            if ( hasFormNumber )
            {
                myQuery = myQuery.setParameter( "formNumber" , formNumber );
            }
            if ( hasRefPeriod )
            {
                myQuery = myQuery.setParameter( "refPeriod" , refPeriod );
            }
            if ( hasCreatedBy )
            {
                myQuery = myQuery.setParameter( "createdBy" , createdBy );
            }
        }
        // and run it
        return myQuery.getResultList();
    }


    /**
     * adds % on either side so that a LIKE search is automatic
     *
     * @param s
     * @return String (surrounded by % wildcards)
     */
    private static String addWildcards( String s )
    {
        if ( !s.startsWith( "%" ) )
        {
            s = "%" + s;
        }
        if ( !s.endsWith( "%" ) )
        {
            s = s + "%";
        }
        return s;
    }


    /**
     * builds a query for the criteria chosen in the
     * CreateNewQnrEditorTopComponent based on the parms (from text fields)
     *
     * @deprecated NOT USED YET
     *
     * @param survey
     * @param refPeriod
     * @param category
     * @param title
     * @return a list of QuestionTitleLinkView object results
     */
    public static List<QuestionTitleLinkView> buildCreateQnrQuery( String survey , String refPeriod , String category , String title )
    {
        Query myQuery;
        final String OPEN_BRACKET = "[";
        boolean useRPs = false;   // unlikely: using a ReportingPeriods reference in the Questionnaires table instead of a regular String

        // if no selected fields, return everything
        if ( ( survey == null || survey.isEmpty() || survey.startsWith( OPEN_BRACKET ) )
                && ( category == null || category.isEmpty() || category.startsWith( OPEN_BRACKET ) )
                && ( title == null || title.isEmpty() || title.startsWith( OPEN_BRACKET ) )
                && ( refPeriod == null || refPeriod.isEmpty() || refPeriod.startsWith( OPEN_BRACKET ) ) )
        {
            //myQuery = entityManager.createNamedQuery( "QuestionTitleLinkView.findAll" );
            myQuery = getEntityManager().createNamedQuery( "QuestionTitleLinkView.findAll" );
        }
        else
        {
            // build the query
            StringBuilder sb = new StringBuilder( "SELECT q FROM QuestionTitleLinkView q, Questionnaires qnr" );

            String where = " WHERE";
            String and = " AND";
            String conj = where;

            boolean hasSurvey = false;
            boolean hasTitle = false;
            boolean hasCategory = false;
            boolean hasRefPeriod = false;

            String addSurvey = "";
            String addTitle = "";
            String addCategory = "";
            String addRefPeriod = "";

            if ( survey != null && !survey.isEmpty() )
            {
                survey = addWildcards( survey );
                addSurvey = conj + " qnr.survey LIKE :survey";
                conj = and;
                hasSurvey = true;
            }
            if ( title != null && !title.isEmpty() )
            {
                title = addWildcards( title );
                addTitle = conj + " q.title LIKE :title";
                conj = and;
                hasTitle = true;
            }
            if ( category != null && !category.isEmpty() )
            {
                category = addWildcards( category );
                addCategory = conj + " q.category LIKE :category";
                conj = and;
                hasCategory = true;
            }

            if ( refPeriod != null && !refPeriod.isEmpty() )
            {

                refPeriod = addWildcards( refPeriod );
                if ( useRPs )
                {
                    // Note that this one is getting refPeriod from a ReportingPeriods object called refPeriod,
                    //  not a direct String.
                    addRefPeriod = conj + " qnr.refPeriod.refPeriod LIKE :refPeriod";
                }
                else
                {
                    addRefPeriod = conj + " qnr.refPeriod LIKE :refPeriod";
                }
                conj = and;
                hasRefPeriod = true;
            }

            sb.append( addSurvey );
            sb.append( addTitle );
            sb.append( addCategory );
            sb.append( addRefPeriod );
            String query = sb.toString();

            // create the Entity Manager Query
            //myQuery = entityManager.createQuery( query );
            myQuery = getEntityManager().createQuery( query );
            if ( hasSurvey )
            {
                myQuery = myQuery.setParameter( "survey" , survey );
            }
            if ( hasTitle )
            {
                myQuery = myQuery.setParameter( "title" , title );
            }
            if ( hasCategory )
            {
                myQuery = myQuery.setParameter( "category" , category );
            }
            if ( hasRefPeriod )
            {
                myQuery = myQuery.setParameter( "refPeriod" , refPeriod );
            }

        }
        // and run it
        return myQuery.getResultList();
    }


    /**
     * does a search through four columns of Questionnaires for a given search
     * term
     *
     * @param survey
     * @param refPeriod
     * @param term -- the search term
     * @return a list of Questionnaires whose title, subtitle,
     * formDisplayNumber, or formNumber contain the search term ("LIKE")
     * Questionnaires.
     */
    public static List<Questionnaires> getQuestionnairesSearchResults( String survey , String refPeriod , String term )
    {
        term = addWildcards( term.toUpperCase() );
        //return entityManager.createQuery(
        return getEntityManager().createQuery(
                "SELECT distinct q "
                + "FROM Questionnaires q "
                + "WHERE q.survey = :survey AND q.refPeriod = :refPeriod AND ("
                + "UPPER(q.title) LIKE :term OR UPPER(q.subTitle) LIKE :term OR UPPER(q.formDisplayNumber) LIKE :term OR UPPER(q.formNumber) LIKE :term )" )
                .setParameter( "survey" , survey )
                .setParameter( "refPeriod" , refPeriod )
                .setParameter( "term" , term )
                .getResultList();
    }


    /**
     * does a search through four columns of Questionnaires and two columns of
     * QuestionTitles for a given search term
     *
     * @param survey
     * @param refPeriod
     * @param term -- the search term
     * @param vers -- the Question version -- ignored if empty
     * @param cat -- the Question Category -- ignored if empty
     * @return a list of all Question Titles whose category or title (or QNR
     * fields above) contain the search term QuestionTitleSearchView.
     */
    public static List<QuestionTitleSearchView> getQuestionTitlesSearchResults( String survey , String refPeriod , String term , String vers , String cat )
    {

        term = addWildcards( term.toUpperCase() );

        TypedQuery<QuestionTitleSearchView> q;
        boolean hasCat = false;
        boolean hasVers = false;
        String catClause = "";
        String versClause = "";

        BigInteger biVersion = BigInteger.ZERO;

        if ( !cat.isEmpty() )
        {
            cat = addWildcards( cat.toUpperCase() );
            hasCat = true;
            catClause = " AND (UPPER( qtsv.category ) LIKE :category)";
        }
        if ( !vers.isEmpty() )
        {
            biVersion = new BigInteger( vers );
            hasVers = true;
            versClause = " AND (qtsv.version = :version) ";
        }

        //q = entityManager.createQuery(
        q = getEntityManager().createQuery(
                "SELECT distinct qtsv "
                + "FROM QuestionTitleSearchView qtsv "
                + "WHERE qtsv.survey = :survey AND qtsv.refPeriod = :refPeriod "
                + catClause + versClause + " AND ("
                + "UPPER(qtsv.questionnairesTitle) LIKE :term OR UPPER(qtsv.subTitle) LIKE :term OR UPPER(qtsv.formDisplayNumber) LIKE :term OR UPPER(qtsv.formNumber) LIKE :term OR "
                + "UPPER(qtsv.questionTitle) LIKE :term OR UPPER(qtsv.category) LIKE :term)" , QuestionTitleSearchView.class );
        q.setParameter( "survey" , survey )
                .setParameter( "refPeriod" , refPeriod )
                .setParameter( "term" , term );

        if ( hasCat )
        {
            q.setParameter( "category" , cat );
        }
        if ( hasVers )
        {
            q.setParameter( "version" , biVersion );
        }

        return q.getResultList();
    }


    /**
     * does a search through a column of Custom Responses, four columns of
     * Questionnaires, and two columns of Question Titles for a given search
     * term
     *
     * @param survey
     * @param refPeriod
     * @param term -- the search term
     * @param vers -- the question version -- ignored if empty
     * @param cat -- the question category -- ignored if empty
     * @return a list of all CCIs which match itemWording or the Qnrs and QTs
     * matching as above (CustomContentItemSearchView).
     */
    public static List<CustomContentItemSearchView> getCustomContentItemsSearchResults( String survey , String refPeriod , String term , String vers , String cat )
    {
        term = addWildcards( term.toUpperCase() );

        TypedQuery<CustomContentItemSearchView> q;
        boolean hasCat = false;
        boolean hasVers = false;
        String catClause = "";
        String versClause = "";

        BigInteger biVersion = BigInteger.ZERO;
        if ( !cat.isEmpty() )
        {
            cat = addWildcards( cat.toUpperCase() );
            hasCat = true;
            catClause = " AND (UPPER( ccisv.category ) LIKE :category)";
        }
        if ( !vers.isEmpty() )
        {
            biVersion = new BigInteger( vers );
            hasVers = true;
            versClause = " AND (ccisv.version = :version) ";
        }

        //q = entityManager.createQuery(
        q = getEntityManager().createQuery(
                "SELECT DISTINCT ccisv "
                + "FROM CustomContentItemSearchView ccisv "
                + "WHERE ccisv.survey = :survey AND ccisv.refPeriod = :refPeriod "
                + catClause + versClause + " AND "
                + "( UPPER(ccisv.itemWording) LIKE :term OR "
                + "UPPER(ccisv.questionnairesTitle) LIKE :term OR UPPER(ccisv.subTitle) LIKE :term OR UPPER(ccisv.formDisplayNumber) LIKE :term OR UPPER(ccisv.formNumber) LIKE :term OR "
                + "UPPER(ccisv.questionTitle) LIKE :term OR UPPER(ccisv.category) LIKE :term)" , CustomContentItemSearchView.class );

        q.setParameter( "survey" , survey )
                .setParameter( "refPeriod" , refPeriod )
                .setParameter( "term" , term );

        if ( hasCat )
        {
            q.setParameter( "category" , cat );
        }
        if ( hasVers )
        {
            q.setParameter( "version" , biVersion );
        }

        return q.getResultList();
    }


    /**
     * does a search for all questions with identical content. This is run by a
     * right-click Action of a node in a search results list.
     *
     * @param survey
     * @param refPeriod
     * @param category
     * @param questionTitle
     * @return list of CCI search views which match all four
     */
    public static List<CustomContentItemSearchView> getSameContentQuestions( String survey , String refPeriod , String category , String questionTitle )
    {
        category = category.toUpperCase();
        questionTitle = questionTitle.toUpperCase();

        //return entityManager.createQuery(
        return getEntityManager().createQuery(
                "SELECT DISTINCT sv "
                + "FROM CustomContentItemSearchView sv "
                + "WHERE sv.survey = :survey AND sv.refPeriod = :refPeriod AND "
                + "( UPPER(sv.category) = :category AND UPPER(sv.questionTitle) = :questionTitle)" )
                .setParameter( "survey" , survey )
                .setParameter( "refPeriod" , refPeriod )
                .setParameter( "category" , category )
                .setParameter( "questionTitle" , questionTitle )
                .getResultList();
    }


    /**
     * Get an SFO clob (encoded, compressed) from the CustomContentLayouts
     * table, one of three SFO types
     *
     * @param type    - which SFO type it is
     * @param questId - the associated QuestionTitle object
     * @return long   - the length of retrieved clob
     * Side effect: populate the clobStr object.
     */
    public long getClobFromDB( SfoEnum type , QuestionTitle questId )
    {
        long clobLen = 0;
        String col = null;
        switch ( type )
        {
            case LAYOUT:
                col = "itemLayout";
                break;
            case DESIGNER:
                col = "designerSfo";
                break;
            case ENCAPSULATED:
                col = "encapsulatedSfo";
                break;
            default:
                break;
        }

        //List<String> ccls = entityManager.createQuery(
        List<String> ccls = getEntityManager().createQuery(
                "SELECT c." + col + " FROM CustomContentLayouts c "
                + "WHERE c.formMedia = 'PAPER' AND c.questId = :questId" )
                .setParameter( "questId" , questId )
                .getResultList();


        Iterator<String> it = ccls.iterator();
        while ( it.hasNext() )
        {
            clobStr = it.next();
            if ( clobStr != null )
            {
                clobLen = clobStr.length();
            }
        }

        return clobLen;
    }


    /**
     * gets the jpeg image of this question from the CustomContentLayouts table as an
     * image (ImageIcon)
     *
     * @param qt the QuestionTitle
     * @return ImageIcon
     */
    public static ImageIcon getJpegFromDB( QuestionTitle qt )
    {
        byte[] imageBytes = null;
        ImageIcon ii = null;
        List<CustomContentLayouts> ccls = getCustomContentLayouts( qt );
        Iterator cclit = ccls.iterator();
        CustomContentLayouts ccl = null;
        while ( cclit.hasNext() )
        {
            ccl = (CustomContentLayouts) cclit.next();
            break; // there should only be one.
        }
        if ( ccl != null )
        {
            imageBytes = ccl.getItemLayoutJpeg();
            if ( imageBytes != null )
            {
                ii = new ImageIcon( imageBytes );
            }
        }
        return ii;
    }


    /**
     * updates the CustomContentLayout with the given clob at the given qt.
     *
     * @param type designerSfo, encapsulatedSfo, or itemLayout
     * @param clobStr the string of the encoded, compressed SFO
     * @param qt the question title for the given layout.
     */
    public static void updateSfoClob( SfoEnum type , String clobStr , QuestionTitle qt )
    {

        List<CustomContentLayouts> ccls = getCustomContentLayouts( qt );
        Iterator cclit = ccls.iterator();
        CustomContentLayouts ccl = null;
        while ( cclit.hasNext() )
        {
            ccl = (CustomContentLayouts) cclit.next();
            break; // there should only be one.
        }

        if ( ccl != null )
        {

            try
            {
                beginTransaction();
                switch ( type )
                {
                    case LAYOUT:
                        ccl.setItemLayout( clobStr );
                        break;
                    case DESIGNER:
                        ccl.setDesignerSfo( clobStr );
                        break;
                    case ENCAPSULATED:
                        ccl.setEncapsulatedSfo( clobStr );
                        break;
                    default:
                        break;
                }
                getEntityManager().persist( ccl );
                //entityManager.persist( ccl );
                commitTransaction();
            }
            catch ( Exception e )
            {
                Exceptions.printStackTrace( e );
                rollbackTransaction();
            }
        }
    }


    /**
     * getter
     *
     * @return Clob
     */
    public Clob getClob()
    {
        return clob;
    }


    /**
     * getter
     *
     * @return JPeg (Blob)
     * @
     */
    public Blob getJpeg()
    {
        return jpegBlob;
    }


    /**
     * getter
     *
     * @return clob string
     */
    public String getClobStr()
    {
        return clobStr;
    }


    /**
     * gets a string which holds the contents of the given Clob
     *
     * @param clob
     * @param clen
     * @return String representation of Clob.
     */
    public String getSfoClobString( Clob clob , long clen )
    {
        String cs = null;
        char[] chars = new char[ (int) clen + 100 ];
        try
        {
            Reader reader = clob.getCharacterStream();
            int rlen = reader.read( chars );
            cs = new String( chars );
        }
        catch ( SQLException | IOException e )
        {
            Exceptions.printStackTrace( e );
        }

        return cs;
    }


    /**
     * returns the maximum version number for this survey/refPeriod
     *
     * @param survey
     * @param refPeriod
     * @return int - Maximum Question Version Number
     */
    public static int getMaxVersion( String survey , String refPeriod )
    {
        List<BigInteger> maxList = getMaxVersionList( survey , refPeriod );
        Iterator it = maxList.iterator();

        BigInteger bigMax = BigInteger.valueOf( Long.parseLong( "0" ) );
        while ( it.hasNext() )
        {
            bigMax = (BigInteger) it.next();
            break;
        }
        return bigMax.intValue();
    }


    /**
     * returns a result with the maximum version number of this survey (and
     * optionally refPeriod).  Should only be one result in the list.
     *
     * @param survey
     * @param refPeriod
     * @return List of BigInteger objects (with the max version)
     */
    private static List<BigInteger> getMaxVersionList( String survey , String refPeriod )
    {
        Query q;
        boolean hasRP = false;
        String rpClause = "";
        if ( !refPeriod.isEmpty() )
        {
            hasRP = true;
            rpClause = "AND sv.refPeriod = :refPeriod";
        }
        //q = entityManager.createQuery(
        q = getEntityManager().createQuery(
                "SELECT MAX(sv.version)  "
                + "FROM CustomContentItemSearchView sv "
                + "WHERE sv.survey = :survey "
                + rpClause )
                .setParameter( "survey" , survey );
        if ( hasRP )
        {
            q.setParameter( "refPeriod" , refPeriod );
        }
        return q.getResultList();
    }


    /**
     * begins a JPA transaction
     *
     * @return true if transaction successfully begun
     */
    public static boolean beginTransaction()
    {
        boolean transactionOpen = false;
        //if ( entityManager.isOpen() )
        if ( getEntityManager().isOpen() )
        {
            transaction = entityManager.getTransaction();
            transaction.begin();
            transactionOpen = true;
        }
        return transactionOpen;
    }


    /**
     * commits a transaction
     *
     * @return success on commit
     */
    public static boolean commitTransaction()
    {
        boolean transactionCommitted = false;
        if ( transaction.isActive() )
        {
            transaction.commit();
            transactionCommitted = true;
        }
        return transactionCommitted;
    }


    /**
     * rolls back a transaction
     *
     * @return success on rollback
     */
    public static boolean rollbackTransaction()
    {
        boolean transactionRolledback = false;
        if ( transaction.isActive() )
        {
            transaction.rollback();
            transactionRolledback = true;
        }
        return transactionRolledback;
    }

}
